<?php
namespace App\Http\Query;

use Illuminate\Support\Facades\DB;
use Log;

/*
 * 郢ｧ�ｽｻ郢晢ｽｼ郢晢ｽｫ陷ｩ�ｿｽ
 */
class SetQuery
{
    public $start_date= "";
    public $end_date= "";
    public $set_id ="";
    public function getSets()
    {
        $sql = 'select * from set_master where  delete_flg = 0 ';

        $allSet = DB::select($sql);

        return $allSet;
    }
    public function getAllSetsInfo($setId)
    {
        $sql = 'select * from set_master where set_id = ? and delete_flg = 0 ';

        $param = [$setId];

        $allSet = DB::select($sql,$param);

        return $allSet;
    }

    public function getAllGoodsInfo($goodsId)
    {
        $sql = 'select * from goods_master where goods_id = ? and delete_flg = 0 ';

        $param = [$goodsId];

        $users = DB::select($sql, $param);
        return $users;
    }

    public function addsetMaster($data)
    {
        return DB::table('set_master')->insert($data);
    }

    public function delSetMaster($set_Id)
    {
        $sql = 'update set_master,set_goods_master set set_goods_master.delete_flg = 1, set_master.delete_flg = 1,set_goods_master.updated_at = now(), set_master.updated_at = now()
                where  set_goods_master.set_id =? and set_master.set_id=?';
        $param = [$set_Id,$set_Id];
        $flag = DB::update($sql,$param);

//         $sql = 'update set_goods_master set delete_flg = 1 ,updated_at = now() where  set_id =?';
//         $param = [$set_Id];
//         $flag = DB::update($sql,$param);
        return $flag;
    }
    public function insertSetMaster($setName, $start_date, $end_date)
    {
        $sql = 'insert into set_master (set_name, start_date ,end_date ,delete_flg,created_at,updated_at) values(?,?,?,0,now(),now()) ';
        $param = [$setName, $start_date, $end_date];
        DB::insert($sql,$param);
        return  DB::getPdo()->lastInsertId();;
    }
    public function updateSetMaster($set_id, $setName, $start_date, $end_date)
    {
        $sql = 'update set_master set set_name = ?, start_date = ? ,end_date = ? where set_id = ? and delete_flg = 0 ';
        $param = [$setName, $start_date, $end_date, $set_id];
        $flag = DB::update($sql,$param);
        return $flag;
    }
    public function deleteSetGoods($set_id) {
        $sql = " delete from set_goods_master where set_id = ?";
        $param = [$set_id];
        $flag = DB::update($sql,$param);
        return $flag;
    }
    public function insSetGoodsMaster($setId, $goodsId)
    {
        //echo "".$setId."||||".$goodsId;exit;
        $sql = 'INSERT INTO set_goods_master(set_id, goods_id, delete_flg, created_at, updated_at) VALUES (?,?,0,now(),now())';
        $param = [$setId, $goodsId];
        $flag = DB::insert($sql, $param);
        return $flag;
    }

    public function viewSetGoodsMaster($set_id)
    {
        $sql = 'SELECT distinct goods_master.goods_id,category_master.category_id,category_master.category_name,goods_master.goods_code,goods_master.goods_name
                FROM goods_master,set_goods_master,category_master
                WHERE goods_master.goods_id = set_goods_master.goods_id
                AND category_master.category_id = goods_master.category_id
                AND category_master.delete_flg = 0
                AND set_goods_master.delete_flg = 0
                AND goods_master.delete_flg = 0
                AND set_goods_master.set_id = ? ' ;
        $param = [$set_id];
        return DB::select($sql, $param);
    }

    //startDateとendDateの データーを挿入する
    public function saveSetMaster($param)
    {
        DB::table('set_master')->insert($param);
        $setId = DB::getPdo()->lastInsertId();
        return $setId;
    }
}